﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;
using CeA.Reports.Atendimento.DataAccess.DTO;
using CeA.Reports.Atendimento.DataAccess.DAO;
using CeA.Reports.Atendimento.DataAccess.Criteria;

namespace CeA.Reports.Atendimento.DataAccess.DAO.Oracle {
    public class OracleRelatorioRankingImpl : RelatorioRanking {
        OracleConnection m_connection = null;
        StringBuilder sql = new StringBuilder();

        /// <summary>
        /// Initializes a new instance of the <see cref="OracleRelatorioRankingImpl"/> class.
        /// </summary>
        public OracleRelatorioRankingImpl() {
            m_connection = OracleDAOFactory.CreateConnection();
        }


        public List<RelatorioRankingDTO> FindRankingLojaBy(RankingFilter filter) {
            OracleCommand cmd = m_connection.CreateCommand();
            OracleDataReader dr = null;

            cmd.Transaction = m_connection.BeginTransaction(IsolationLevel.ReadCommitted);

            preparePackage(cmd, filter);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "P_QMATIC_RANKING.sp_relatorio_loja";
            cmd.Parameters.Add("io_cursor", OracleType.Cursor).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("dataInicio", OracleType.DateTime).Value = filter.DataInicial;
            cmd.Parameters.Add("dataFim", OracleType.DateTime).Value = filter.DataFinal;
            cmd.Parameters.Add("tempoAtendimento", OracleType.Int32).Value = filter.TempoDesistencia;
            cmd.Parameters.Add("tempoEsperaMaior", OracleType.Int32).Value = filter.TempoEsperaMaior;
            cmd.Parameters.Add("tempoAtendMaior", OracleType.Int32).Value = filter.TempoAtendimentoMaior;
          
            List<RelatorioRankingDTO> rst = new List<RelatorioRankingDTO>();

            try {
                dr = cmd.ExecuteReader();

                while (dr.Read()) {
                    RelatorioRankingDTO dto = new RelatorioRankingDTO();

                    dto.RankingLoja                         = Convert.ToInt32(dr["RANKING_LOJA"]);
                    dto.RankingGrupo                        = Convert.ToInt32(dr["RANKING_GRUPO"]);
                    dto.SiglaEspecialista                   = Convert.ToString(dr["ESPECIALISTA_SIGLA"]);
                    dto.NomeRfs                             = Convert.ToString(dr["NOME_RFS"]);
                    dto.SiglaRegiao                         = Convert.ToString(dr["SIGLA_REGIAO"]);
                    dto.SiglaLoja                           = Convert.ToString(dr["SIGLA_LOJA"]);
                    dto.LojaId                              = Convert.ToInt32(dr["ID_LOJA"]);
                    dto.TipoSemana                          = Convert.ToInt32(dr["TIPO_SEMANA"]);
                    dto.TempoMaximoEsperaResultado          = Convert.ToInt32(dr["TMAX_ESP_RES"]);
                    dto.TempoMaximoEsperaPosicao            = Convert.ToInt32(dr["TMAX_ESP_POS"]);
                    dto.TempoMedioEsperaResultado           = Convert.ToInt32(dr["TMED_ESP_RES"]);
                    dto.TempoMedioEsperaPosicao             = Convert.ToInt32(dr["TMED_ESP_POS"]);
                    dto.PercentualAtendimento15minResultado = (float)Convert.ToDouble(dr["POR_ATEND_15MIN_RES"]);
                    dto.PercentualAtendimento15minPosicao   = Convert.ToInt32(dr["POR_ATEND_15MIN_POS"]);
                    dto.PercentualDesistenciaResultado      = (float)Convert.ToDouble(dr["POR_DESIS_RES"]);
                    dto.PercentualDesistenciaPosicao        = Convert.ToInt32(dr["POR_DESIS_POS"]);
                    dto.TempoMedioAtendimentoResultado      = Convert.ToInt32(dr["TMED_ATEN_RES"]);
                    dto.Total                               = Convert.ToInt32(dr["TOTAL"]);
                    dto.PosicaoSemanaAnterior               = Convert.ToInt32(dr["POSICAO_SEM_ANT"]);
                    dto.PosicaoPenultimaSemana              = Convert.ToInt32(dr["POSICAO_PENULTIMA_SEM"]);

                    rst.Add(dto);
                }

                cmd.Transaction.Commit();
            }
            catch (Exception ex) {
                throw ex;
            }
            finally {
                m_connection.Close();
            }
            return rst;
        }


        public List<RelatorioRankingDTO> FindRankingEspecialistaBy(RankingFilter filter) {
            OracleCommand cmd = m_connection.CreateCommand();
            OracleDataReader dr = null;

            cmd.Transaction = m_connection.BeginTransaction(IsolationLevel.ReadCommitted);

            preparePackage(cmd, filter);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "P_QMATIC_RANKING.sp_relatorio_especialista";
            cmd.Parameters.Add("io_cursor", OracleType.Cursor).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("dataInicio", OracleType.DateTime).Value = filter.DataInicial;
            cmd.Parameters.Add("dataFim", OracleType.DateTime).Value = filter.DataFinal;
            cmd.Parameters.Add("tempoAtendimento", OracleType.Int32).Value = filter.TempoDesistencia;
            cmd.Parameters.Add("tempoEsperaMaior", OracleType.Int32).Value = filter.TempoEsperaMaior;
            cmd.Parameters.Add("tempoAtendMaior", OracleType.Int32).Value = filter.TempoAtendimentoMaior;

            List<RelatorioRankingDTO> rst = new List<RelatorioRankingDTO>();

            try {
                dr = cmd.ExecuteReader();

                while (dr.Read()) {
                    RelatorioRankingDTO dto = new RelatorioRankingDTO();

                    dto.RankingLoja = Convert.ToInt32(dr["RANKING_LOJA"]);
                    dto.RankingGrupo = Convert.ToInt32(dr["RANKING_GRUPO"]);
                    dto.SiglaEspecialista = Convert.ToString(dr["ESPECIALISTA_SIGLA"]);
                    dto.NomeRfs = Convert.ToString(dr["NOME_RFS"]);
                    dto.SiglaRegiao = Convert.ToString(dr["SIGLA_REGIAO"]);
                    dto.SiglaLoja = Convert.ToString(dr["SIGLA_LOJA"]);
                    dto.LojaId = Convert.ToInt32(dr["ID_LOJA"]);
                    dto.TipoSemana = Convert.ToInt32(dr["TIPO_SEMANA"]);
                    dto.TempoMaximoEsperaResultado = Convert.ToInt32(dr["TMAX_ESP_RES"]);
                    dto.TempoMaximoEsperaPosicao = Convert.ToInt32(dr["TMAX_ESP_POS"]);
                    dto.TempoMedioEsperaResultado = Convert.ToInt32(dr["TMED_ESP_RES"]);
                    dto.TempoMedioEsperaPosicao = Convert.ToInt32(dr["TMED_ESP_POS"]);
                    dto.PercentualAtendimento15minResultado = (float)Convert.ToDouble(dr["POR_ATEND_15MIN_RES"]);
                    dto.PercentualAtendimento15minPosicao = Convert.ToInt32(dr["POR_ATEND_15MIN_POS"]);
                    dto.PercentualDesistenciaResultado = (float)Convert.ToDouble(dr["POR_DESIS_RES"]);
                    dto.PercentualDesistenciaPosicao = Convert.ToInt32(dr["POR_DESIS_POS"]);
                    dto.TempoMedioAtendimentoResultado = Convert.ToInt32(dr["TMED_ATEN_RES"]);
                    dto.Total = Convert.ToInt32(dr["TOTAL"]);
                    dto.PosicaoSemanaAnterior = Convert.ToInt32(dr["POSICAO_SEM_ANT"]);
                    dto.PosicaoPenultimaSemana = Convert.ToInt32(dr["POSICAO_PENULTIMA_SEM"]);

                    rst.Add(dto);
                }

                cmd.Transaction.Commit();
            }
            catch (Exception ex) {
                throw ex;
            } finally {
                m_connection.Close();
            }
            return rst;
        }


        private void preparePackage(OracleCommand cmd, RankingFilter filter) {
            cmd.CommandType = CommandType.StoredProcedure;

            foreach (int id in filter.RfsList) {
                cmd.CommandText = "P_QMATIC_RANKING.sp_add_rfs";
                cmd.Parameters.Add("p_rfs", OracleType.Int32).Value = id;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }

            foreach (int id in filter.RegiaoList) {
                cmd.CommandText = "P_QMATIC_RANKING.sp_add_regiao";
                cmd.Parameters.Add("p_regiao", OracleType.Int32).Value = id;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }

            foreach (int id in filter.LojaList) {
                cmd.CommandText = "P_QMATIC_RANKING.sp_add_loja";
                cmd.Parameters.Add("p_loja", OracleType.Int32).Value = id;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }

            foreach (int id in filter.EspecialistaList) {
                cmd.CommandText = "P_QMATIC_RANKING.sp_add_especialista";
                cmd.Parameters.Add("p_especialista", OracleType.Int32).Value = id;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
        }

        public List<RelatorioRankingDTO> FindRankingLojaRfsBy(RankingFilter filter) {
            OracleCommand cmd = m_connection.CreateCommand();
            OracleDataReader dr = null;

            cmd.Transaction = m_connection.BeginTransaction(IsolationLevel.ReadCommitted);

            preparePackage(cmd, filter);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "P_QMATIC_RANKING.sp_relatorio_bloco_rfs";
            cmd.Parameters.Add("io_cursor", OracleType.Cursor).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("dataInicio", OracleType.DateTime).Value = filter.DataInicial;
            cmd.Parameters.Add("dataFim", OracleType.DateTime).Value = filter.DataFinal;
            cmd.Parameters.Add("tempoAtendimento", OracleType.Int32).Value = filter.TempoDesistencia;
            cmd.Parameters.Add("tempoEsperaMaior", OracleType.Int32).Value = filter.TempoEsperaMaior;
            cmd.Parameters.Add("tempoAtendMaior", OracleType.Int32).Value = filter.TempoAtendimentoMaior;

            List<RelatorioRankingDTO> rst = new List<RelatorioRankingDTO>();

            try {
                dr = cmd.ExecuteReader();

                while (dr.Read()) {
                    RelatorioRankingDTO dto = new RelatorioRankingDTO();

                    dto.RankingLoja = Convert.ToInt32(dr["RANKING_LOJA"]);
                    dto.RankingGrupo = Convert.ToInt32(dr["RANKING_GRUPO"]);
                    dto.SiglaEspecialista = Convert.ToString(dr["ESPECIALISTA_SIGLA"]);
                    dto.NomeRfs = Convert.ToString(dr["NOME_RFS"]);
                    dto.SiglaRegiao = Convert.ToString(dr["SIGLA_REGIAO"]);
                    dto.SiglaLoja = Convert.ToString(dr["SIGLA_LOJA"]);
                    dto.LojaId = Convert.ToInt32(dr["ID_LOJA"]);
                    dto.TipoSemana = Convert.ToInt32(dr["TIPO_SEMANA"]);
                    dto.TempoMaximoEsperaResultado = Convert.ToInt32(dr["TMAX_ESP_RES"]);
                    dto.TempoMaximoEsperaPosicao = Convert.ToInt32(dr["TMAX_ESP_POS"]);
                    dto.TempoMedioEsperaResultado = Convert.ToInt32(dr["TMED_ESP_RES"]);
                    dto.TempoMedioEsperaPosicao = Convert.ToInt32(dr["TMED_ESP_POS"]);
                    dto.PercentualAtendimento15minResultado = (float)Convert.ToDouble(dr["POR_ATEND_15MIN_RES"]);
                    dto.PercentualAtendimento15minPosicao = Convert.ToInt32(dr["POR_ATEND_15MIN_POS"]);
                    dto.PercentualDesistenciaResultado = (float)Convert.ToDouble(dr["POR_DESIS_RES"]);
                    dto.PercentualDesistenciaPosicao = Convert.ToInt32(dr["POR_DESIS_POS"]);
                    dto.TempoMedioAtendimentoResultado = Convert.ToInt32(dr["TMED_ATEN_RES"]);
                    dto.Total = Convert.ToInt32(dr["TOTAL"]);
                    dto.PosicaoSemanaAnterior = Convert.ToInt32(dr["POSICAO_SEM_ANT"]);
                    dto.PosicaoPenultimaSemana = Convert.ToInt32(dr["POSICAO_PENULTIMA_SEM"]);

                    rst.Add(dto);
                }

                cmd.Transaction.Commit();
            }
            catch (Exception ex) {
                throw ex;
            } finally {
                m_connection.Close();
            }
            return rst;
        }

        #region RelatorioRanking Members


        public RelatorioRankingDTO FindRankingLojaConsolidadoBy(RankingFilter filter) {
            OracleCommand cmd = m_connection.CreateCommand();
            OracleDataReader dr = null;

            cmd.Transaction = m_connection.BeginTransaction(IsolationLevel.ReadCommitted);

            preparePackage(cmd, filter);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "P_QMATIC_RANKING.sp_relatorio_bloco_brasil";
            cmd.Parameters.Add("io_cursor", OracleType.Cursor).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("dataInicio", OracleType.DateTime).Value = filter.DataInicial;
            cmd.Parameters.Add("dataFim", OracleType.DateTime).Value = filter.DataFinal;
            cmd.Parameters.Add("tempoAtendimento", OracleType.Int32).Value = filter.TempoDesistencia;
            cmd.Parameters.Add("tempoEsperaMaior", OracleType.Int32).Value = filter.TempoEsperaMaior;
            cmd.Parameters.Add("tempoAtendMaior", OracleType.Int32).Value = filter.TempoAtendimentoMaior;

            RelatorioRankingDTO dto = new RelatorioRankingDTO();

            try {
                dr = cmd.ExecuteReader();

                

                if(dr.Read()) { 
                    dto.RankingLoja = Convert.ToInt32(dr["RANKING_LOJA"]);
                    dto.RankingGrupo = Convert.ToInt32(dr["RANKING_GRUPO"]);
                    dto.SiglaEspecialista = Convert.ToString(dr["ESPECIALISTA_SIGLA"]);
                    dto.NomeRfs = Convert.ToString(dr["NOME_RFS"]);
                    dto.SiglaRegiao = Convert.ToString(dr["SIGLA_REGIAO"]);
                    dto.SiglaLoja = Convert.ToString(dr["SIGLA_LOJA"]);
                    dto.LojaId = Convert.ToInt32(dr["ID_LOJA"]);
                    dto.TipoSemana = Convert.ToInt32(dr["TIPO_SEMANA"]);
                    dto.TempoMaximoEsperaResultado = Convert.ToInt32(dr["TMAX_ESP_RES"]);
                    dto.TempoMaximoEsperaPosicao = Convert.ToInt32(dr["TMAX_ESP_POS"]);
                    dto.TempoMedioEsperaResultado = Convert.ToInt32(dr["TMED_ESP_RES"]);
                    dto.TempoMedioEsperaPosicao = Convert.ToInt32(dr["TMED_ESP_POS"]);
                    dto.PercentualAtendimento15minResultado = (float)Convert.ToDouble(dr["POR_ATEND_15MIN_RES"]);
                    dto.PercentualAtendimento15minPosicao = Convert.ToInt32(dr["POR_ATEND_15MIN_POS"]);
                    dto.PercentualDesistenciaResultado = (float)Convert.ToDouble(dr["POR_DESIS_RES"]);
                    dto.PercentualDesistenciaPosicao = Convert.ToInt32(dr["POR_DESIS_POS"]);
                    dto.TempoMedioAtendimentoResultado = Convert.ToInt32(dr["TMED_ATEN_RES"]);
                    dto.Total = Convert.ToInt32(dr["TOTAL"]);
                    dto.PosicaoSemanaAnterior = Convert.ToInt32(dr["POSICAO_SEM_ANT"]);
                    dto.PosicaoPenultimaSemana = Convert.ToInt32(dr["POSICAO_PENULTIMA_SEM"]);
                }

                cmd.Transaction.Commit();
            }
            catch (Exception ex) {
                throw ex;
            } finally {
                m_connection.Close();
            }
            return dto;
        }

        #endregion
    }
}
